package com.jeeplus.modules.ap.util;

import com.jeeplus.modules.ap.entity.*;
import com.jeeplus.modules.fee.entity.Profit;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * Created by lichaohui on 2018/4/26.
 */
public  class Export {
    /**
     * 收入导出
     * @param response
     * @param list
     */
    public  void  exportincome(HttpServletResponse response,  List<Income> list){
        //excel标题
        String[] title = {"单号","项目","经办人","日期","科目","金额","费用对象","费用归属","票据类型","票据号码","交款对象","账户","说明"};


        //excel文件名
        String fileName = "日常收入表.xls";

        //sheet名
        String sheetName = "日常收入";
//创建HSSFWorkbook
        HSSFWorkbook wb = income(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook income(String sheetName,String []title,List<Income> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getDocumentNumber());
            row.createCell(1).setCellValue(list.get(i).getProjectName().getName());
            row.createCell(2).setCellValue(list.get(i).getResponsiblePerson().getName());
            row.createCell(3).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(4).setCellValue(list.get(i).getOneSubject());
            row.createCell(5).setCellValue(list.get(i).getMoney());
            row.createCell(6).setCellValue(list.get(i).getCostObject());
            row.createCell(7).setCellValue(list.get(i).getCostAffiliation());
            row.createCell(8).setCellValue(list.get(i).getBillType());
            row.createCell(9).setCellValue(list.get(i).getBillNumber());
            row.createCell(10).setCellValue(list.get(i).getPaymentObject());
            row.createCell(11).setCellValue(list.get(i).getAccount());
            row.createCell(12).setCellValue(list.get(i).getRemarks());

        }
        return wb;
    }

    /**
     * 支出导出
     * @param response
     * @param list
     */
    public  void  exportexpenditure(HttpServletResponse response,  List<Expenditure> list){
        //excel标题
        String[] title = {"单号","项目","经办人","日期","科目","金额","支出类别","费用对象","费用归属","票据类型","票据号码","说明"};


        //excel文件名
        String fileName = "费用支出表.xls";

        //sheet名
        String sheetName = "费用支出";
//创建HSSFWorkbook
        HSSFWorkbook wb = expenditure(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook expenditure(String sheetName,String []title,List<Expenditure> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getDocumentNumber());
            row.createCell(1).setCellValue(list.get(i).getProjectName().getName());
            row.createCell(2).setCellValue(list.get(i).getResponsiblePerson().getName());
            row.createCell(3).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(4).setCellValue(list.get(i).getOneSubject());
            row.createCell(5).setCellValue(list.get(i).getMoney());
            row.createCell(6).setCellValue(list.get(i).getType());
            row.createCell(7).setCellValue(list.get(i).getCostObject());
            row.createCell(8).setCellValue(list.get(i).getCostAffiliation());
            row.createCell(9).setCellValue(list.get(i).getBillType());
            row.createCell(10).setCellValue(list.get(i).getBillNumber());
            row.createCell(11).setCellValue(list.get(i).getRemarks());

        }
        return wb;
    }



    /**
     * 备用金导出
     * @param response
     * @param list
     */
    public  void  exportimprest(HttpServletResponse response,  List<Imprest> list){
        //excel标题
        String[] title = {"申请日期","项目","申请人","单据号","申请理由","申请类别","申请金额","到账日期","到账金额","付款人"};


        //excel文件名
        String fileName = "备用金表.xls";

        //sheet名
        String sheetName = "备用金";
//创建HSSFWorkbook
        HSSFWorkbook wb = imprest(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook imprest(String sheetName,String []title,List<Imprest> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(sdf.format(list.get(i).getApplicationDate()));
            row.createCell(1).setCellValue(list.get(i).getProjectName());
            row.createCell(2).setCellValue(list.get(i).getApplicant());
            row.createCell(3).setCellValue(list.get(i).getNumber());
            row.createCell(4).setCellValue(list.get(i).getReason());
            row.createCell(5).setCellValue(list.get(i).getMoney());
            row.createCell(6).setCellValue(list.get(i).getTotalMoney());
            row.createCell(7).setCellValue(sdf.format(list.get(i).getDzrq()));
            row.createCell(8).setCellValue(list.get(i).getPaymentMoney());
            row.createCell(9).setCellValue(list.get(i).getPayer());
        }
        return wb;
    }

    /**
     * 预付款导出
     * @param response
     * @param list
     */
    public  void  exportadvance(HttpServletResponse response,  List<Advance> list){
        //excel标题
        String[] title = {"项目名称","签订日期","合同号","租车协议号","状态","承运人","承运人车牌号",
                "司机电话","到站地","预付现金","预付油卡","实付现金","实付油卡",
                "油卡卡号","实付总金额","增减说明","付款时间","入账日期"};


        //excel文件名
        String fileName = "预付款表.xls";

        //sheet名
        String sheetName = "预付款";
//创建HSSFWorkbook
        HSSFWorkbook wb = advance(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook advance(String sheetName,String []title,List<Advance> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getPro());
            row.createCell(1).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(2).setCellValue(list.get(i).getCode());
            row.createCell(3).setCellValue(list.get(i).getZccode());
            row.createCell(4).setCellValue(list.get(i).getStatus());
            row.createCell(5).setCellValue(list.get(i).getPerson());
            row.createCell(6).setCellValue(list.get(i).getCard());
            row.createCell(7).setCellValue(list.get(i).getPhone());
            row.createCell(8).setCellValue(list.get(i).getArrive());
            row.createCell(9).setCellValue(list.get(i).getMoney());
            row.createCell(10).setCellValue(list.get(i).getOil());
            row.createCell(11).setCellValue(list.get(i).getSfxj());
            row.createCell(12).setCellValue(list.get(i).getSfyk());
            row.createCell(13).setCellValue(list.get(i).getNumber());
            row.createCell(14).setCellValue(list.get(i).getSfzje());
            row.createCell(15).setCellValue(list.get(i).getRemarks());
            row.createCell(16).setCellValue(sdf.format(list.get(i).getFkdate()));
            if(list.get(i).getRz().equals("")){
                row.createCell(17).setCellValue("");
            }else{
                row.createCell(17).setCellValue(sdf.format(list.get(i).getRz()));
            }

        }
        return wb;
    }

    /**
     * 车队收支报表导出
     * @param response
     * @param list
     */
    public  void  exportveh(HttpServletResponse response,  List<Map<String,Object>> list){
        //excel标题
        String[] title = {"时间","车牌号","总趟数","收入","维修费","加油费","路桥费",
                "住宿费","轮胎费","防护费","产品损耗","其它费用","车辆折旧"};


        //excel文件名
        String fileName = "车队收支报表.xls";

        //sheet名
        String sheetName = "车队收支报表";
//创建HSSFWorkbook
        HSSFWorkbook wb = veh(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook veh(String sheetName,String []title,List<Map<String,Object>> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).get("date")+"");
            row.createCell(1).setCellValue(list.get(i).get("car")+"");
            row.createCell(2).setCellValue(list.get(i).get("ts")+"");
            row.createCell(3).setCellValue(list.get(i).get("sr")+"");
            row.createCell(4).setCellValue(list.get(i).get("wxf")+"");
            row.createCell(5).setCellValue(list.get(i).get("jyf")+"");
            row.createCell(6).setCellValue(list.get(i).get("lqf")+"");
            row.createCell(7).setCellValue(list.get(i).get("zsf")+"");
            row.createCell(8).setCellValue(list.get(i).get("lt")+"");
            row.createCell(9).setCellValue(list.get(i).get("fh")+"");
            row.createCell(10).setCellValue(list.get(i).get("sh")+"");
            row.createCell(11).setCellValue(list.get(i).get("qt")+"");
            row.createCell(12).setCellValue(list.get(i).get("zj")+"");


        }
        return wb;
    }






    /**
     * 保险导出
     * @param response
     * @param list
     */
    public  void  exportlnsurance(HttpServletResponse response,  List<Lnsurance> list){
        //excel标题
        String[] title = {"项目名称","起运地","到站地","发货日期","车牌号","运单号","保单名称",
                "货物名称","包装","件数","重量","运输方式","货物估算金额"};


        //excel文件名
        String fileName = "保险明细表.xls";

        //sheet名
        String sheetName = "保险";
//创建HSSFWorkbook
        HSSFWorkbook wb = lnsurance(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook lnsurance(String sheetName,String []title,List<Lnsurance> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getPro());
            row.createCell(1).setCellValue(list.get(i).getStart());
            row.createCell(2).setCellValue(list.get(i).getArrive());
            row.createCell(3).setCellValue(sdf.format(list.get(i).getFhrq()));
            row.createCell(4).setCellValue(list.get(i).getCph());
            row.createCell(5).setCellValue(list.get(i).getYdh());
            row.createCell(6).setCellValue(list.get(i).getHwmc());
            row.createCell(7).setCellValue(list.get(i).getName());
            row.createCell(8).setCellValue(list.get(i).getBz());
            row.createCell(9).setCellValue(list.get(i).getJs());
            row.createCell(10).setCellValue(list.get(i).getZl());
            row.createCell(11).setCellValue(list.get(i).getYsfs());
            row.createCell(12).setCellValue(list.get(i).getGsje());
        }
        return wb;
    }


    /**
     * 回执结算导出
     * @param response
     * @param list
     */
    public  void  exportrecel(HttpServletResponse response,  List<Recel> list){
        //excel标题
        String[] title = {"项目","签订时间","合同号","租车协议号","状态","租车协议金额",
                "承运人","车牌号","司机电话","到站地","重量","数量","货到合同金额",
                "实付货到金额","回执合同金额","回执协议金额","实付回执现金","实付回执油卡","油卡卡号","实付总金额"
                ,"结算日期","领款人","增减说明","回执单号", "入账日期"
        };


        //excel文件名
        String fileName = "回执结算表.xls";

        //sheet名
        String sheetName = "回执结算";
//创建HSSFWorkbook
        HSSFWorkbook wb = recel(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook recel(String sheetName,String []title,List<Recel> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getPro());
            row.createCell(1).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(2).setCellValue(list.get(i).getCode());
            row.createCell(3).setCellValue(list.get(i).getZccode());
            row.createCell(4).setCellValue(list.get(i).getStatus());
            row.createCell(5).setCellValue(list.get(i).getZcje());
            row.createCell(6).setCellValue(list.get(i).getPerson());
            row.createCell(7).setCellValue(list.get(i).getCard());
            row.createCell(8).setCellValue(list.get(i).getPhone());
            row.createCell(9).setCellValue(list.get(i).getArrive());
            row.createCell(10).setCellValue(list.get(i).getWeight());
            row.createCell(11).setCellValue(list.get(i).getNum());
            row.createCell(12).setCellValue(list.get(i).getHdht());
            row.createCell(13).setCellValue(list.get(i).getSfhd());
            row.createCell(14).setCellValue(list.get(i).getHzht());
            row.createCell(15).setCellValue(list.get(i).getHzxy());
            row.createCell(16).setCellValue(list.get(i).getMoney());
            row.createCell(17).setCellValue(list.get(i).getOil());
            row.createCell(18).setCellValue(list.get(i).getNumber());
            row.createCell(19).setCellValue(list.get(i).getSfzje());
            row.createCell(20).setCellValue(sdf.format(list.get(i).getJsdate()));
            row.createCell(21).setCellValue(list.get(i).getDriver());
            row.createCell(22).setCellValue(list.get(i).getRemarks());
            row.createCell(23).setCellValue(list.get(i).getReceipt());
            if(list.get(i).getRz().equals("")){
                row.createCell(24).setCellValue("");
            }else{
                row.createCell(24).setCellValue(sdf.format(list.get(i).getRz()));
            }



        }
        return wb;
    }


    /**
     * 运单附加费导出
     * @param response
     * @param list
     */
    public  void  exportbillAdd(HttpServletResponse response,  List<BillAdd> list){
        //excel标题
        String[] title = {"项目","时间","装运单号","合同号","租车协议号",
                "状态","信息费收入","保险费收入","防护品收入","其他收入","防护品支出",
                "装卸费支出","其它支出","入账日期"};


        //excel文件名
        String fileName = "运单附加费表.xls";

        //sheet名
        String sheetName = "运单附加费";
//创建HSSFWorkbook
        HSSFWorkbook wb = billAdd(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook billAdd(String sheetName,String []title,List<BillAdd> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getPro());
            row.createCell(1).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(2).setCellValue(list.get(i).getZydh());
            row.createCell(3).setCellValue(list.get(i).getHth());
            row.createCell(4).setCellValue(list.get(i).getZch());
            row.createCell(5).setCellValue(list.get(i).getStatus());
            row.createCell(6).setCellValue(list.get(i).getXxfsr());
            row.createCell(7).setCellValue(list.get(i).getBxfsr());
            row.createCell(8).setCellValue(list.get(i).getFhpsr());
            row.createCell(9).setCellValue(list.get(i).getQtsr());
            row.createCell(10).setCellValue(list.get(i).getFhpzc());
            row.createCell(11).setCellValue(list.get(i).getZxfzc());
            row.createCell(12).setCellValue(list.get(i).getQtzc());
            if(list.get(i).getRz().equals("")){
                row.createCell(13).setCellValue("");
            }else{
                row.createCell(13).setCellValue(sdf.format(list.get(i).getRz()));
            }


        }
        return wb;
    }





    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"iso-8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
      }
